When querying Oracle Database, you can use the SPOOL command to export your query results to a text file when using SQLcl.

Here’s an example that exports a whole table:

Here’s what it did, line by line:

Here’s what the resulting file looks like:

In my case, my SQLFORMAT was set to ansiconsole, and so the output reflects that.

You can change this to any format that suits.

Here’s an example of exporting the result in a format that can be used as an import file with sql*loader:

Result:

In this case I also reset the SQLFORMAT back to ansiconsole once the SPOOL operation had completed.

You can remove the X rows selected with SET FEEDBACK off:

Result:

In this case I turned FEEDBACK back on after exporting the file.

By default, SPOOL uses REPLACE, which replaces the file if it already exists.

However, we can use the APPEND argument to append the results to the file.

Example:

Resulting file:

This example appended the results to the file that was created (and populated) in the previous example. This resulted in the result being duplicated in the file.

We can use REPLACE to overwrite the existing file with the results of a new query:

Resulting file:

As mentioned, REPLACE is the default setting, so we could have simply omitted this argument altogether.